package com.katian.etat_compte;

import au.com.bytecode.opencsv.CSVReader;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.text.ParseException;
import java.util.*;

import static com.katian.ExcelUtil.*;

/**
 * User: csavard
 * Date: 13-05-14
 */
public class MergerUtil {

    public static final int CAISSE_COLUMN = 9;
    public static final String DOLLARS_FORMAT = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)";
    private static final int ACHAT_SHEET = 3;
    public static final int COMMISSION_INDEX = 1;
    public static final int COMMISSION_AMOUNT_COL = 14;
    public static final int COMMISSION_DIFF_CELL = 15;

    public static Map<String, CommissionItem> getCommissionItem(InputStream in) throws IOException {
        Map<String, CommissionItem> itemsMap = new LinkedHashMap<>();

        Workbook wb = new XSSFWorkbook(in);
        Sheet sheet = wb.getSheetAt(0);
        int rowindex = 1;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {

                Cell nameCell = row.getCell(2);
                if (nameCell != null) {

                    if (nameCell.getCellType() == Cell.CELL_TYPE_STRING) {
                        String name = nameCell.getStringCellValue();
                        CommissionItem item = new CommissionItem();


                        String externalDocumentNo = null;
                        Cell externalDocumentCell = row.getCell(4);
                        if (externalDocumentCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            externalDocumentNo = String.valueOf((long) externalDocumentCell.getNumericCellValue());
                        } else {
                            externalDocumentNo = row.getCell(4).getStringCellValue();
                        }

                        String description = row.getCell(7).getStringCellValue();
                        double amount = row.getCell(9).getNumericCellValue();

                        item.setName(name);
                        if (externalDocumentNo.startsWith("BC")) {
                            String idString = externalDocumentNo.substring(1, externalDocumentNo.length());
                            if (idString.endsWith(".")) {
                                idString = idString.substring(0, idString.length() - 1);
                            }
                            item.setId(idString);
                            item.setType(CommissionItem.BB);
                        } else if (externalDocumentNo.startsWith("BI")) {
                            String idString = externalDocumentNo.substring(1, externalDocumentNo.length());
                            if (idString.endsWith(".")) {
                                idString = idString.substring(0, idString.length() - 1);
                            }
                            item.setId(idString);
                            item.setType(CommissionItem.BB);
                        } else if (externalDocumentNo.startsWith("B0")) {
                            String idString = externalDocumentNo.substring(5, externalDocumentNo.length());
                            if (idString.endsWith(".")) {
                                idString = idString.substring(0, idString.length() - 1);
                            }
                            item.setId(idString);
                            item.setType(CommissionItem.BB);
                        } else if (externalDocumentNo.startsWith("0")) {
                            String idString = description;
                            if (idString.startsWith("0")) {
                                idString = idString.substring(4, idString.length());
                            }

                            idString = idString.substring(0, idString.length() - 2);
                            item.setId(idString);
                        } else if (externalDocumentNo.startsWith("I")) {
                            String idString = description;
                            idString = idString.substring(0, idString.length() - 2);
                            item.setId(idString);
                        } else {
                            item.setName(description);
                        }

                        item.setAmount(amount);
                        CommissionItem commissionItem = itemsMap.get(item.getId());
                        if (commissionItem == null) {
                            itemsMap.put(item.getId(), item);
                        } else {
                            commissionItem.setAmount(commissionItem.getAmount() + item.getAmount());
                        }
                    }
                }
            }
            rowindex++;
        }
        return itemsMap;
    }

    public static Map<Long, Produit> getProducts(InputStream in) throws IOException {
        Map<Long, Produit> productMap = new LinkedHashMap<>();

        Workbook wb = new XSSFWorkbook(in);
        Sheet sheet = wb.getSheetAt(0);
        int rowindex = 1;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                Produit produit = new Produit();
                Cell cell = row.getCell(1);
                if (cell != null) {
                    double code = cell.getNumericCellValue();
                    String name = row.getCell(2).getStringCellValue();
                    double uniteParCaisse = row.getCell(4).getNumericCellValue();

                    produit.setCode((long) code);

                    produit.setNom(name);
                    produit.setUniteParCaisse((int) uniteParCaisse);

                    productMap.put(produit.getCode(), produit);
                }
            }
            rowindex++;
        }
        return productMap;
    }

    public static Map<String, Achat> getAchats(InputStream in) throws IOException {
        Map<String, Achat> achatMap = new LinkedHashMap<>();

        Workbook wb = new XSSFWorkbook(in);
        Sheet sheet = wb.getSheetAt(0);
        int rowindex = 1;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                Cell invoiceCell = row.getCell(8);
                Cell documentNoCell = row.getCell(3);
                Cell amountCell = row.getCell(12);

                if (invoiceCell != null) {
                    Achat achat = new Achat();
                    String invoice;
                    String documentNo;
                    double amount = 0;


                    if (invoiceCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        invoice = "0000" + String.valueOf((long) invoiceCell.getNumericCellValue());
                    } else {
                        invoice = invoiceCell.getStringCellValue();
                        if (invoice.toLowerCase().startsWith("invoice 0")) {
                            invoice = invoice.substring(8, invoice.length() - 2);
                        } else if (invoice.toLowerCase().startsWith("invoice i")) {
                            invoice = invoice.substring(8, invoice.length() - 2);
                        } else if (invoice.toLowerCase().startsWith("credit memo")) {
                            if (Character.isAlphabetic(invoice.charAt(invoice.length() - 1))) {
                                invoice = invoice.substring(12, invoice.length() - 2);
                            } else {
                                invoice = invoice.substring(12, invoice.length());
                            }
                        }
                    }

                    if (documentNoCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        documentNo = String.valueOf(documentNoCell.getNumericCellValue());
                    } else {
                        documentNo = documentNoCell.getStringCellValue();
                    }

                    if (amountCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        achat.setAmount(amountCell.getNumericCellValue());
                    }

                    achat.setInvoice(invoice);

                    achat.setDocumentNo(documentNo);

                    Achat achat1 = achatMap.get(invoice);
                    if (achat1 == null) {
                        achatMap.put(achat.getInvoice(), achat);
                    } else {
                        achat1.setAmount(achat1.getAmount() + achat.getAmount());
                    }
                } else {
                    System.err.println("Il n'y a pas de invoice à la ligne " + rowindex);
                }

            }
            rowindex++;
        }
        return achatMap;
    }

    public static Map<String, Commande> getVentes(InputStream in, Map<Long, Produit> produits) throws Exception {
        Map<String, Commande> commandeMap = new LinkedHashMap<>();

        String[] nextLine;
        CSVReader reader = new CSVReader(new InputStreamReader(in), ';');
        nextLine = reader.readNext();
        if (nextLine.length <= 1) {
            reader = new CSVReader(new InputStreamReader(in), ',');
            nextLine = reader.readNext();
            if (nextLine.length <= 1) {
                throw new Exception("Format fichier inconnu");
            }
        }

        while ((nextLine = reader.readNext()) != null) {
            try {
                String noLivraison = nextLine[0];
                noLivraison = noLivraison.substring(1, noLivraison.length() - 1);
                String retailerName = nextLine[17];
                String noString = nextLine[31];
                if (noString.length() > 2) {
                    noString = noString.substring(1, noString.length() - 1);
                    long no = Long.valueOf(noString);
                    Commande commande = commandeMap.get(noLivraison);
                    if (commande == null) {
                        commande = new Commande();
                        commande.setNo(no);
                        commandeMap.put(noLivraison, commande);
                    }

                    String productCodeString = nextLine[5];
                    long productCode = Long.valueOf(productCodeString.substring(1, productCodeString.length() - 1));

                    Produit produit = produits.get(productCode);
                    if (produit == null) {
                        produit = new Produit();
                        produit.setCode(productCode);
                        produit.setNom("Produit inconnue");
                        produit.setUnknow(true);
                    }
                    String packString = nextLine[7];
                    int pack = Integer.parseInt(packString);

                    Collection<Commande.ProduitItem> produitItems = commande.getProduitItems();
                    if (produitItems == null) {
                        produitItems = new Vector<>();
                        commande.setProduitItems(produitItems);
                    }

                    Commande.ProduitItem item = new Commande.ProduitItem();
                    item.setRetailerName(retailerName);
                    item.setProduit(produit);
                    item.setNb(pack);
                    produitItems.add(item);
                }

            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return commandeMap;
    }

    public static Map<String, Distributeur> getDistributeurs(InputStream in) throws IOException {
        Map<String, Distributeur> distributeurMap = new LinkedHashMap<>();

        Workbook wb = new XSSFWorkbook(in);
        Sheet sheet = wb.getSheetAt(0);
        int colndex = 3;
        while (colndex <= 9) {
            String name = sheet.getRow(3).getCell(colndex).getStringCellValue();
            double tauxClasseA = sheet.getRow(6).getCell(colndex).getNumericCellValue();
            double tauxClasseB = sheet.getRow(7).getCell(colndex).getNumericCellValue();

            Distributeur distributeur = new Distributeur();
            distributeur.setName(name);
            distributeur.setTauxClasseA(tauxClasseA);
            distributeur.setTauxClasseB(tauxClasseB);
            distributeurMap.put(distributeur.getName(), distributeur);

            colndex++;
        }
        return distributeurMap;
    }

    public static String getCustomerName(XSSFWorkbook wb) {
        XSSFSheet sheet = wb.getSheetAt(0);
        Row customerNameRow = sheet.getRow(7);
        Cell cell = customerNameRow.getCell(2);
        String customerName = cell.getStringCellValue();
        return customerName;
    }

    public static String getPeriod(XSSFWorkbook wb) {
        XSSFSheet sheet = wb.getSheetAt(0);
        Row customerNameRow = sheet.getRow(6);
        Cell cell = customerNameRow.getCell(2);
        String customerName = cell.getStringCellValue();
        return customerName;
    }

    public static void generateCommision(XSSFWorkbook wb, XSSFWorkbook wbDestination, Map<String, CommissionItem> commissionItemMap) throws IOException, ParseException {
        DataFormat dataFormat = wbDestination.createDataFormat();

        CellStyle dateCellStyle = wbDestination.createCellStyle();
        dateCellStyle.setDataFormat(dataFormat.getFormat("yyyy-mm-dd"));

        CellStyle dollarsCellStyle = wbDestination.createCellStyle();
        dollarsCellStyle.setDataFormat(dataFormat.getFormat(DOLLARS_FORMAT));

        CellStyle percentageCellStyle = wbDestination.createCellStyle();
        percentageCellStyle.setDataFormat(dataFormat.getFormat("0.00%"));


        XSSFSheet originalSheet = wb.getSheetAt(COMMISSION_INDEX);
        XSSFSheet sheet = wbDestination.createSheet("Commission");
        copySheet(originalSheet, sheet);
        formatHeader(sheet);

        Row titleRow = sheet.getRow(6);
        titleRow.createCell(13).setCellValue("REFERENCE");
        titleRow.createCell(COMMISSION_DIFF_CELL).setCellValue("DIFF");

        int rowindex = 8;
        Map<String, String> ids = new HashMap<>();
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                //delete la premiere ligne en shiftant les autres lignes par en haut
                try {
                    sheet.shiftRows(rowindex, rowindex, -1);
                } catch (Exception e) {
                    System.err.println("Pas capable de shifter la ligne: " + rowindex);
                    e.printStackTrace();
                }

                Cell invoiceDateCell = row.getCell(0);
                Cell idCell = row.getCell(2);
                Cell orderNoCell = row.getCell(3);
                Cell netTotalCell = row.getCell(6);
                Cell commPercentCell = row.getCell(7);
                Cell commDollarsCell = row.getCell(8);
                Cell tpsCell = row.getCell(9);
                Cell tvqCell = row.getCell(10);
                Cell commDueCell = row.getCell(11);
                Cell confirmedCell = row.getCell(12);
                Cell nameCell = row.getCell(13);


                invoiceDateCell.setCellStyle(dateCellStyle);
                confirmedCell.setCellStyle(dateCellStyle);

                String idString = idCell.getStringCellValue();
                long id = 0;
                if (idString.startsWith("0000")) {
                    idString = idString.substring(4, idString.length());
                    id = Long.valueOf(idString);
                    idCell.setCellValue(id);
                }

                ids.put(idString, idString);

                if (orderNoCell != null) {
                    if (orderNoCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                        orderNoCell.setCellValue(orderNoCell.getNumericCellValue());
                    } else {
                        String orderNoCellString = orderNoCell.getStringCellValue();
                        if (orderNoCellString != null && !orderNoCellString.isEmpty()) {
                            orderNoCell.setCellValue(Double.valueOf(orderNoCellString));
                        }
                    }
                }

                netTotalCell.setCellStyle(dollarsCellStyle);
                commDollarsCell.setCellStyle(dollarsCellStyle);
                tpsCell.setCellStyle(dollarsCellStyle);
                tvqCell.setCellStyle(dollarsCellStyle);
                commDueCell.setCellStyle(dollarsCellStyle);


                commPercentCell.setCellStyle(percentageCellStyle);
                CommissionItem commissionItem = commissionItemMap.get(idString);
                if (commissionItem != null) {
                    nameCell.setCellValue(commissionItem.getName());

                    Cell amountCell = row.createCell(COMMISSION_AMOUNT_COL);
                    amountCell.setCellValue(commissionItem.getAmount());

                    Cell diffCell = row.createCell(COMMISSION_DIFF_CELL);
                    diffCell.setCellFormula("L" + rowindex + "+O" + rowindex);
                    diffCell.setCellStyle(dollarsCellStyle);
                }

            }
            rowindex++;
        }

        for (String id : commissionItemMap.keySet()) {
            CommissionItem commissionItem = commissionItemMap.get(id);
            if (ids.get(id) == null && commissionItem.getType().equals(CommissionItem.BS)) {
                Row row = sheet.createRow(rowindex);
                Cell idCell = row.createCell(2);
                idCell.setCellValue(commissionItem.getId());

                Cell amountCell = row.createCell(COMMISSION_AMOUNT_COL);
                amountCell.setCellValue(commissionItem.getAmount());

                Cell diffCell = row.createCell(COMMISSION_DIFF_CELL);
                diffCell.setCellFormula("L" + rowindex + "+O" + rowindex);
                diffCell.setCellStyle(dollarsCellStyle);

                rowindex++;
            }
        }

        autoResizeAllColumn(sheet);
    }


    public static void generateBuyBack(XSSFWorkbook wb, XSSFWorkbook wbDestination, Map<String, CommissionItem> commissionItemMap, Map<String, Commande> ventes, Map<String, Distributeur> distributeurs) throws IOException {
        DataFormat dataFormat = wbDestination.createDataFormat();

        CellStyle dateCellStyle = wbDestination.createCellStyle();
        dateCellStyle.setDataFormat(dataFormat.getFormat("yyyy-mm-dd"));

        CellStyle dollarsCellStyle = wbDestination.createCellStyle();
        dollarsCellStyle.setDataFormat(dataFormat.getFormat(DOLLARS_FORMAT));

        CellStyle percentageCellStyle = wbDestination.createCellStyle();
        percentageCellStyle.setDataFormat(dataFormat.getFormat("0.00%"));

        CellStyle numberCellStyle = null;


        XSSFSheet originalSheet = wb.getSheetAt(2);
        XSSFSheet sheet = wbDestination.createSheet("Buy Back");
        copySheet(originalSheet, sheet);
        Row rowTodelete = sheet.getRow(3);
        if (rowTodelete != null) {
            sheet.removeRow(rowTodelete);
        }

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 3));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3));
        sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 3));

        String clientName = sheet.getRow(2).getCell(0).getStringCellValue();
        Distributeur distributeur = null;

        Collection<String> distributeurNames = distributeurs.keySet();
        for (String disName : distributeurNames) {
            if (clientName.startsWith(disName) || disName.startsWith(clientName)) {
                distributeur = distributeurs.get(disName);
                break;
            }
        }


        Row titleRow = sheet.getRow(6);
        insertColumn((XSSFRow) titleRow, CAISSE_COLUMN);
        titleRow.getCell(CAISSE_COLUMN).setCellValue("Caisse");
        sheet.removeRow(sheet.getRow(7));
        Map<String, String> ids = new HashMap<>();
        int rowindex = 8;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                insertColumn((XSSFRow) row, CAISSE_COLUMN);
                //delete la premiere ligne en shiftant les autres lignes par en haut
                try {
                    sheet.shiftRows(rowindex, rowindex, -1);
                } catch (Exception e) {
                    System.err.println("Pas capable de shifter la ligne: " + rowindex);
                    e.printStackTrace();
                }

                Cell dateCell = row.getCell(0);
                Cell noLivraisonCell = row.getCell(3);
                Cell noCommandCell = row.getCell(4);
                if (noCommandCell == null) {
                    noCommandCell = row.createCell(4);
                }
                Cell classeCell = row.getCell(6);
                Cell totalCell = row.getCell(7);
                XSSFCell commPercentCell = (XSSFCell) row.getCell(8);
                Cell caisseCell = row.getCell(CAISSE_COLUMN);
                Cell commDollarCell = row.getCell(10);
                Cell tpsCell = row.getCell(11);
                Cell tvqCell = row.getCell(12);
                Cell commDueCell = row.getCell(13);
                Cell confirmeCell = row.getCell(14);
                Cell nameCell = row.getCell(15);
                if (nameCell == null) {
                    nameCell = row.createCell(15);
                }

                dateCell.setCellStyle(dateCellStyle);
                confirmeCell.setCellStyle(dateCellStyle);

                String noLivraisonString = noLivraisonCell.getStringCellValue();
                String idString = noLivraisonString;
                long id = 0;
                if (noLivraisonString.startsWith("0000")) {
                    idString = noLivraisonString.substring(4, noLivraisonString.length());
                    id = Long.valueOf(idString);
                    noLivraisonCell.setCellValue(id);
                }

                ids.put(idString, idString);

                long noCommande = 0;

                if (noCommandCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                    noCommande = (long) noCommandCell.getNumericCellValue();
                    noCommandCell.setCellValue(noCommande);
                } else {
                    String orderNoCellString = noCommandCell.getStringCellValue();
                    if (orderNoCellString != null && !orderNoCellString.isEmpty()) {
                        noCommande = Long.parseLong(orderNoCellString);
                        noCommandCell.setCellValue(noCommande);
                    }
                }

                totalCell.setCellStyle(dollarsCellStyle);
                commDollarCell.setCellStyle(dollarsCellStyle);
                tpsCell.setCellStyle(dollarsCellStyle);
                tvqCell.setCellStyle(dollarsCellStyle);
                commDueCell.setCellStyle(dollarsCellStyle);

                commPercentCell.setCellStyle(dollarsCellStyle);
                String classValue = classeCell.getStringCellValue();
                if (classValue.equalsIgnoreCase("A") || classValue.equalsIgnoreCase("B")) {
                    if (classValue.equalsIgnoreCase("A")) {
                        if (distributeur != null) {
                            commPercentCell.setCellValue(distributeur.getTauxClasseA());
                        } else {
                            setCellComment(commPercentCell, "Pas de distributeur");
                            setRowBackground(row, IndexedColors.ROSE);
                        }
                    } else if (classValue.equalsIgnoreCase("B")) {
                        if (distributeur != null) {
                            commPercentCell.setCellValue(distributeur.getTauxClasseB());
                        } else {
                            setCellComment(commPercentCell, "Pas de distributeur");
                            setRowBackground(row, IndexedColors.ROSE);
                        }
                    }

                    if (numberCellStyle == null) {
                        numberCellStyle = wbDestination.createCellStyle();
                        numberCellStyle.cloneStyleFrom(caisseCell.getCellStyle());
                        short format = dataFormat.getFormat("0.00");
                        numberCellStyle.setDataFormat(format);
                    }
                    caisseCell.setCellStyle(numberCellStyle);

                    Commande commande = ventes.get(noLivraisonString);
                    if (commande != null) {
                        try {
                            double value = totalCell.getNumericCellValue();
                            if (value >= 0) {
                                caisseCell.setCellValue(commande.getNbCaisse());
                            } else {
                                caisseCell.setCellValue(commande.getNbCaisse() * -1.0);
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                            setCellComment(caisseCell, e.toString());
                            setRowBackground(row, IndexedColors.AQUA);
                        }
                    } else {
                        setCellComment(caisseCell, "Pas trouvé la commande");
                        setRowBackground(row, IndexedColors.AQUA);
                    }

                    commDollarCell.setCellFormula("J" + rowindex + "*" + "I" + rowindex);
                    tpsCell.setCellFormula("K" + rowindex + "*0.05");
                    tvqCell.setCellFormula("K" + rowindex + "*1.05*0.095");
                    commDueCell.setCellFormula("K" + rowindex + "+L" + rowindex + "+M" + rowindex);
                } else {
                    commPercentCell.setCellStyle(percentageCellStyle);
                }

                CommissionItem commissionItem = commissionItemMap.get(idString);


                if (commissionItem != null) {
                    nameCell.setCellValue(commissionItem.getName());
                    Cell amountCell = row.createCell(16);
                    amountCell.setCellValue(commissionItem.getAmount());

                    Cell diffCell = row.createCell(17);
                    diffCell.setCellFormula("N" + rowindex + "+Q" + rowindex);
                    diffCell.setCellStyle(dollarsCellStyle);

                } else {
                    setCellComment(nameCell, "Pas de commission: " + idString);
                    nameCell.setCellValue("?");
                    setRowBackground(row, IndexedColors.YELLOW);
                }
            }
            rowindex++;
        }

        for (String id : commissionItemMap.keySet()) {
            CommissionItem commissionItem = commissionItemMap.get(id);
            if (ids.get(id) == null && commissionItem.getType().equals(CommissionItem.BB)) {
                Row row = sheet.createRow(rowindex);
                Cell idCell = row.createCell(3);
                idCell.setCellValue(commissionItem.getId());

                Cell amountCell = row.createCell(16);
                amountCell.setCellValue(commissionItem.getAmount());

                Cell diffCell = row.createCell(17);
                diffCell.setCellFormula("N" + rowindex + "+Q" + rowindex);
                diffCell.setCellStyle(dollarsCellStyle);
                rowindex++;
            }
        }

        autoResizeAllColumn(sheet);
    }


    public static void generateAchat(XSSFWorkbook wb, XSSFWorkbook wbDestination, Map<String, Achat> achats) {
        CellStyle dateCellStyle = null;
        CellStyle dollarsCellStyle = null;


        XSSFSheet originalSheet = wb.getSheetAt(ACHAT_SHEET);
        XSSFSheet sheet = wbDestination.createSheet("Achats");
        copySheet(originalSheet, sheet);
        formatHeader(sheet);

        int rowindex;

        //delete les ligne qui ne sont pas des données
        rowindex = 7;
        while (rowindex < sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                XSSFCell cell = (XSSFCell) row.getCell(0);
                if (cell.getCellType() != XSSFCell.CELL_TYPE_NUMERIC) {
                    sheet.removeRow(row);
                    sheet.shiftRows(rowindex + 1, sheet.getLastRowNum(), -1);
                    rowindex--;
                }
            }
            rowindex++;
        }

        //formattage  des ligne
        sheet.getRow(6).getCell(10).setCellValue("REFERENCE");

        rowindex = 7;
        Map<String, String> invoices = new HashMap<>();

        while (rowindex <= sheet.getLastRowNum()) {
            XSSFRow row = sheet.getRow(rowindex);
            if (row != null) {
                XSSFCell cell = row.getCell(0);
                if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                    XSSFCell invoiceDateCell = row.getCell(0);
                    XSSFCell invoiceNoCell = row.getCell(2);
                    XSSFCell orderNoCell = row.getCell(3);
                    XSSFCell netTotalCell = row.getCell(5);
                    XSSFCell tpsCell = row.getCell(6);
                    XSSFCell tvqCell = row.getCell(7);
                    XSSFCell totalCell = row.getCell(8);
                    XSSFCell confirmationCell = row.getCell(9);
                    XSSFCell refCell = row.getCell(10);


                    String invoiceNoStr = getStringValue(invoiceNoCell);
                    if (invoiceNoStr.startsWith("0")) {
                        Long value = Long.valueOf(invoiceNoStr);
                        invoiceNoCell.setCellValue(value);
                    }

                    invoices.put(invoiceNoStr, invoiceNoStr);

                    if (orderNoCell != null) {
                        if (orderNoCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                            orderNoCell.setCellValue((long) orderNoCell.getNumericCellValue());
                        } else {
                            String orderNoStr = orderNoCell.getStringCellValue();
                            if (orderNoStr != null && orderNoStr.startsWith("0")) {
                                orderNoCell.setCellValue(Long.valueOf(orderNoStr));
                            }
                        }
                    }

                    String tps = tpsCell.getStringCellValue();
                    if (!tps.isEmpty()) {
                        tpsCell.setCellValue(Double.valueOf(tps));
                    }

                    String tvq = tvqCell.getStringCellValue();
                    if (!tvq.isEmpty()) {
                        tvqCell.setCellValue(Double.valueOf(tvq));
                    }


                    //Set le format des cellules
                    if (dateCellStyle == null) {
                        dateCellStyle = getDateCellStyle(wbDestination, invoiceDateCell);
                    }
                    if (dollarsCellStyle == null) {
                        dollarsCellStyle = getDollarsCellStyle(wbDestination, netTotalCell);
                    }

                    invoiceDateCell.setCellStyle(dateCellStyle);
                    netTotalCell.setCellStyle(dollarsCellStyle);
                    tpsCell.setCellStyle(dollarsCellStyle);
                    tvqCell.setCellStyle(dollarsCellStyle);
                    totalCell.setCellStyle(dollarsCellStyle);
                    confirmationCell.setCellStyle(dateCellStyle);

                    removeCell(row, 11);
                    removeCell(row, 12);
                    removeCell(row, 13);

                    Achat achat = achats.get(invoiceNoStr);

                    if (achat != null) {
                        refCell.setCellValue(achat.getDocumentNo());
                        Cell amountCell = row.createCell(11);
                        amountCell.setCellValue(achat.getAmount());

                        Cell diffCell = row.createCell(12);
                        diffCell.setCellFormula("I" + (rowindex + 1) + "-L" + (rowindex + 1));
                        diffCell.setCellStyle(dollarsCellStyle);
                    } else {
                        refCell.setCellValue("?");
                    }
                }
            }
            rowindex++;
        }

        Set<String> invoiceAchats = achats.keySet();
        for (String invoiceAchat : invoiceAchats) {
            if (invoices.get(invoiceAchat) == null) {
                Row row = sheet.createRow(rowindex);
                Cell invoiceNoCell = row.createCell(2);
                invoiceNoCell.setCellValue(invoiceAchat);

                Cell amountCell = row.createCell(11);
                amountCell.setCellValue(achats.get(invoiceAchat).getAmount());

                Cell diffCell = row.createCell(12);
                diffCell.setCellFormula("I" + (rowindex + 1) + "-L" + (rowindex + 1));
                diffCell.setCellStyle(dollarsCellStyle);

                rowindex++;
            }
        }

        autoResizeAllColumn(sheet);
    }

    public static void generateUnconfirmedBS(XSSFWorkbook wb, XSSFWorkbook wbDestination) {
        CellStyle dateCellStyle = null;
        CellStyle dollarsCellStyle = null;


        XSSFSheet originalSheet = wb.getSheetAt(5);
        XSSFSheet sheet = wbDestination.createSheet("Unconfirmed BS");
        copySheet(originalSheet, sheet);
        formatHeader(sheet);

        //Insert la column No Client
        XSSFRow titleRow = (XSSFRow) sheet.getRow(6);
        insertColumn(titleRow, 4);
        titleRow.getCell(4).setCellValue(titleRow.getCell(7).getStringCellValue());

        //formattage  des ligne
        int rowindex = 7;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                //Insert la column No Client
                Cell confirmationDateCell = row.getCell(9);

                Long dateCellValue = null;
                if (confirmationDateCell != null && confirmationDateCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                    dateCellValue = (long) confirmationDateCell.getNumericCellValue();
                }
                insertColumn((XSSFRow) row, 4);

                Cell dateFactureCell = row.getCell(0);
                Cell noFactureCell = row.getCell(2);
                Cell orderNoCell = row.getCell(3);
                Cell newNoClientCell = row.getCell(4);
                Cell totalCell = row.getCell(6);
                Cell oldNoClientCell = row.getCell(7);
                Cell distributeurCell = row.getCell(8);
                Cell sousDistributeurCell = row.getCell(9);
                String invoiceNoStr = noFactureCell.getStringCellValue();
                if (invoiceNoStr.startsWith("0")) {
                    noFactureCell.setCellValue(Long.valueOf(invoiceNoStr));
                }

                if (orderNoCell != null) {
                    String orderNoStr = orderNoCell.getStringCellValue();
                    if (orderNoStr.startsWith("0")) {
                        orderNoCell.setCellValue(Long.valueOf(orderNoStr));
                    }
                }

                newNoClientCell.setCellValue(oldNoClientCell.getStringCellValue());

                //Set le format des cellules
                if (dateCellStyle == null) {
                    dateCellStyle = getDateCellStyle(wbDestination, dateFactureCell);
                }
                if (dollarsCellStyle == null) {
                    dollarsCellStyle = getDollarsCellStyle(wbDestination, totalCell);
                }

                dateFactureCell.setCellStyle(dateCellStyle);
                if (dateCellValue != null) {
                    Cell newConfirmationCell = row.createCell(10);
                    newConfirmationCell.setCellValue(dateCellValue);
                    newConfirmationCell.setCellStyle(dateCellStyle);
                }
                totalCell.setCellStyle(dollarsCellStyle);

                //Efface les valeur inutile
                // removeCell(row, 7, 9);
            }
            rowindex++;
        }

        autoResizeAllColumn(sheet);
    }

    public static void generateUnconfirmedBB(XSSFWorkbook wb, XSSFWorkbook wbDestination) {
        CellStyle dateCellStyle = null;
        CellStyle dollarsCellStyle = null;


        XSSFSheet originalSheet = wb.getSheetAt(6);
        XSSFSheet sheet = wbDestination.createSheet("Unconfirmed BB");
        copySheet(originalSheet, sheet);
        formatHeader(sheet);

        XSSFRow titleRow = (XSSFRow) sheet.getRow(6);

        //formattage  des ligne
        int rowindex = 7;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                Cell dateCell = row.getCell(0);
                Cell noLivraisonCell = row.getCell(4);
                Cell noCommandeCell = row.getCell(5);
                Cell totalCell = row.getCell(7);
                Cell entrepotCell = row.getCell(8);
                Cell confirmationCell = row.getCell(9);
                Cell sousDistributeurCell = row.getCell(10);
                Cell validCell = row.getCell(11);

                String noLivraison = noLivraisonCell.getStringCellValue();
                if (noLivraison.startsWith("0")) {
                    noLivraisonCell.setCellValue(Long.valueOf(noLivraison));
                }

                if (noCommandeCell != null) {
                    String noCommandeStr = noCommandeCell.getStringCellValue();
                    if (noCommandeStr.startsWith("0")) {
                        noCommandeCell.setCellValue(Long.valueOf(noCommandeStr));
                    }
                }

                //Set le format des cellules
                if (dateCellStyle == null) {
                    dateCellStyle = getDateCellStyle(wbDestination, dateCell);
                }
                if (dollarsCellStyle == null) {
                    dollarsCellStyle = getDollarsCellStyle(wbDestination, totalCell);
                }

                dateCell.setCellStyle(dateCellStyle);
                confirmationCell.setCellStyle(dateCellStyle);
                totalCell.setCellStyle(dollarsCellStyle);

                //Efface les valeur inutile
                // removeCell(row, 8, 8);
                //removeCell(row, 10, row.getLastCellNum());
            }
            rowindex++;
        }

        autoResizeAllColumn(sheet);
    }


    public static void generateBBAvecCaisses(XSSFWorkbook wbDestination, Map<String, Commande> ventes) throws IOException {
        DataFormat dataFormat = wbDestination.createDataFormat();

        CellStyle dateCellStyle = wbDestination.createCellStyle();
        dateCellStyle.setDataFormat(dataFormat.getFormat("yyyy-mm-dd"));

        CellStyle dollarsCellStyle = wbDestination.createCellStyle();
        dollarsCellStyle.setDataFormat(dataFormat.getFormat(DOLLARS_FORMAT));

        CellStyle percentageCellStyle = wbDestination.createCellStyle();
        percentageCellStyle.setDataFormat(dataFormat.getFormat("0.00%"));

        CellStyle numberCellStyle = null;


        XSSFSheet sheet = wbDestination.createSheet("Buy Back");


        Row titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("Détaillant");
        titleRow.createCell(1).setCellValue("Commande");
        titleRow.createCell(2).setCellValue("Produit");
        titleRow.createCell(3).setCellValue("Caisses");
        int rowIndex = 1;
        Iterator<String> iterator = ventes.keySet().iterator();
        while (iterator.hasNext()) {
            String commandeName = iterator.next();
            Commande commande = ventes.get(commandeName);
            if (commande != null && commande.getProduitItems() != null) {
                Iterator<Commande.ProduitItem> produitItemIterator = commande.getProduitItems().iterator();
                while (produitItemIterator.hasNext()) {
                    Commande.ProduitItem product = produitItemIterator.next();
                    XSSFRow row = sheet.createRow(rowIndex);
                    row.createCell(0).setCellValue(product.getRetailerName());
                    row.createCell(1).setCellValue(commandeName);
                    row.createCell(2).setCellValue(product.getProduit().getCode());
                    try {
                        row.createCell(3).setCellValue(product.getNbCaisse());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    rowIndex++;
                }
            }
        }
        autoResizeAllColumn(sheet);
    }

    public static void formatHeader(XSSFSheet sheet) {
        XSSFRow row;
        row = sheet.getRow(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        removeCell(row, 4, row.getLastCellNum());

        row = sheet.getRow(1);
        removeCell(row, 4, row.getLastCellNum());

        row = sheet.getRow(2);
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3));
        removeCell(row, 4, row.getLastCellNum());

        row = sheet.getRow(3);
        if (row != null) {
            sheet.removeRow(row);
        }

        row = sheet.getRow(4);
        sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 3));
        removeCell(row, 4, row.getLastCellNum());

        row = sheet.getRow(5);
        if (row != null) {
            sheet.removeRow(row);
        }
    }


    public static CellStyle getDateCellStyle(XSSFWorkbook wb, Cell template) {
        DataFormat dataFormat = wb.createDataFormat();

        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.cloneStyleFrom(template.getCellStyle());
        short format = dataFormat.getFormat("yyyy-mm-dd");
        cellStyle.setDataFormat(format);

        return cellStyle;
    }

    public static CellStyle getDollarsCellStyle(XSSFWorkbook wb, Cell template) {
        DataFormat dataFormat = wb.createDataFormat();

        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.cloneStyleFrom(template.getCellStyle());
        short format = dataFormat.getFormat(DOLLARS_FORMAT);
        cellStyle.setDataFormat(format);

        return cellStyle;
    }
}
